package com.plpm.util;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

/**
 * SQLite本地日期时间类型处理器
 * 用于处理SQLite中DATETIME字段与Java LocalDateTime的转换
 * 
 * @author PLPM Team
 * @version 1.0
 */
public class SqliteLocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> {

    private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter.format(FORMATTER));
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String dateTimeStr = rs.getString(columnName);
        return parseDateTime(dateTimeStr);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String dateTimeStr = rs.getString(columnIndex);
        return parseDateTime(dateTimeStr);
    }

    @Override
    public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String dateTimeStr = cs.getString(columnIndex);
        return parseDateTime(dateTimeStr);
    }

    private LocalDateTime parseDateTime(String dateTimeStr) {
        if (dateTimeStr == null || dateTimeStr.trim().isEmpty()) {
            return null;
        }
        
        String trimmed = dateTimeStr.trim();
        
        try {
            // 1. 尝试标准格式 yyyy-MM-dd HH:mm:ss
            return LocalDateTime.parse(trimmed, FORMATTER);
        } catch (Exception e) {
            try {
                // 2. 尝试ISO-8601格式（默认解析器，支持带T分隔符和纳秒）
                // 格式如: 2025-11-03T09:43:14 或 2025-11-03T09:43:14.680171200
                return LocalDateTime.parse(trimmed);
            } catch (Exception e2) {
                try {
                    // 3. 尝试ISO格式（带T，无小数点）
                    return LocalDateTime.parse(trimmed, DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss"));
                } catch (Exception e3) {
                    try {
                        // 4. 尝试标准格式带毫秒 yyyy-MM-dd HH:mm:ss.SSS
                        return LocalDateTime.parse(trimmed, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"));
                    } catch (Exception e4) {
                        try {
                            // 5. 尝试ISO格式带毫秒 yyyy-MM-dd'T'HH:mm:ss.SSS
                            return LocalDateTime.parse(trimmed, DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS"));
                        } catch (Exception e5) {
                            try {
                                // 6. 处理带纳秒的情况：截断或填充到9位纳秒
                                if (trimmed.contains("T") && trimmed.contains(".")) {
                                    // 格式: yyyy-MM-ddTHH:mm:ss.nnnnnnnnn 或 yyyy-MM-ddTHH:mm:ss.nnn
                                    int dotIndex = trimmed.indexOf('.');
                                    int tIndex = trimmed.indexOf('T');
                                    
                                    if (dotIndex > tIndex && dotIndex < trimmed.length() - 1) {
                                        // 提取日期时间部分和纳秒部分
                                        String dateTimePart = trimmed.substring(0, dotIndex);
                                        String nanoPart = trimmed.substring(dotIndex + 1);
                                        
                                        // 找到纳秒部分的结束位置（可能是数字结尾或后面还有其他字符）
                                        int nanoEnd = nanoPart.length();
                                        for (int i = 0; i < nanoPart.length(); i++) {
                                            if (!Character.isDigit(nanoPart.charAt(i))) {
                                                nanoEnd = i;
                                                break;
                                            }
                                        }
                                        
                                        String nanoStr = nanoPart.substring(0, nanoEnd);
                                        
                                        // 将纳秒部分标准化为9位（不足补0，超过截断）
                                        if (nanoStr.length() < 9) {
                                            StringBuilder sb = new StringBuilder(nanoStr);
                                            while (sb.length() < 9) {
                                                sb.append('0');
                                            }
                                            nanoStr = sb.toString();
                                        } else if (nanoStr.length() > 9) {
                                            nanoStr = nanoStr.substring(0, 9);
                                        }
                                        
                                        String normalized = dateTimePart + "." + nanoStr;
                                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSSSSSSS");
                                        return LocalDateTime.parse(normalized, formatter);
                                    }
                                }
                                
                                throw new RuntimeException("无法解析日期时间字符串: " + dateTimeStr, e5);
                            } catch (Exception e6) {
                                throw new RuntimeException("无法解析日期时间字符串: " + dateTimeStr + " (尝试了多种格式均失败)", e6);
                            }
                        }
                    }
                }
            }
        }
    }
}